<?php
class ControllerStockMoneyOut extends Controller {
	private $error = array();
	
	//仓库初始化
	public function index() {
		$this->load->language('stock/money_out');
		

		$this->document->setTitle($this->language->get('heading_title'));
		// var_dump($this->language->get('heading_title'));die();
		$this->load->model('stock/money_out');
		$this->load->model('stock/money_in');

		$this->getList();

    }

	private function getList(){

		$page = I('get.page');
	    if(!$page){
	      $page = 1;
	    }
	    $data = I('get.');
	    // var_dump($data);
	    $this->document->addScript('view/javascript/angular.js');
	    $this->document->addScript('view/javascript/bhz_ctl.js');
	    if(!empty($data['filter_date_start'])){
	      $filter_date_start = $data['filter_date_start'];
	    }else{
	      $filter_date_start = '0000-00-00';
	    }

	    if(!empty($data['filter_date_end'])){
	      $filter_date_end = $data['filter_date_end'];
	    }else{
	      $filter_date_end = '9999-12-31';
	    }

	    $filter['money_out.date_added'] = array('between',array($filter_date_start,$filter_date_end));

	    if(isset($data['filter_status'])){
	      $filter_status = trim($data['filter_status']);
	      if($filter_status!='*'){
	        $filter['money_out.status'] = $filter_status;
	      }  
	    }else{
	      $data['filter_status'] = '*'; 
	      $filter['money_out.status'] = array('neq',4);
	    }
	    if(isset($data['filter_type'])){
	      $filter_type = trim($data['filter_type']);
	      if($filter_type!='*'){
	        $filter['money_out.refer_type_id'] = $filter_type;
	      }  
	    }else{
	     	 $data['filter_type'] = '*';
	    }
	   
	   if(!empty($data['refer_id'])){
	      $refer_id = trim($data['refer_id']);
	      $filter['money_out.refer_id'] = array('like',"%".$refer_id."%");
	    }
	    $flag = false;
	    if(!empty($data['payee'])){
	    	$flag = true;
			$payee = trim($data['payee']);
	      	$filter['money_out.payee'] = array('like',"%".$payee."%");
	    }
		//获取数据
		$data['status_array'] = $this->model_stock_money_out->getstatus();
		$data['status_array'][] = array('money_out_status_id'=>'*','name'=>'全部');
		// var_dump($data['status_array']);
		$data['money_type'] = $this->model_stock_money_in->getMoneyType(2);
		$data['money_type'][] = array('money_type_id'=>'*','type_name'=>'全部');


	    $money_list =  $this->model_stock_money_out->getList($page, $filter);
	    $total_payables = 0;
	    foreach ($money_list as $key => $value) {
	    	if ($value['status']!=2) {
	    		$money_list[$key]['all_color']='red';
	    	}
	    	if ($flag) {
	    		$total_payables+=$value['payables'];
	    	}
	    }
	    $data['total_payables'] = $total_payables;
	    $total = $this->model_stock_money_out->getListCount($filter);
	    $pagination = new Pagination();
	    $pagination->total = $total;
	    $pagination->page = $page;
	    $pagination->limit = $this->config->get('config_limit_admin');
	    $pagination->url = $this->url->link('stock/money_out', 'token=' . $this->session->data['token'] . '&page={page}'.'&filter_date_end='.$filter_date_end.'&filter_date_start='.$filter_date_start.'&filter_status='.$data['filter_status'].'&payee='.$data['payee'].'&refer_id='.$data['refer_id'].'&filter_type='.$data['filter_type'], 'SSL');
		$data['pagination'] = $pagination->render();
	    $data['money_list'] = $money_list;
		$data['results'] = sprintf($this->language->get('text_pagination'), ($total) ? (($page - 1) * $this->config->get('config_limit_admin')) + 1 : 0, ((($page - 1) * $this->config->get('config_limit_admin')) > ($total - $this->config->get('config_limit_admin'))) ? $total : ((($page - 1) * $this->config->get('config_limit_admin')) + $this->config->get('config_limit_admin')), $total, ceil($total / $this->config->get('config_limit_admin')));

			/*面包屑导航栏*/
			$data['heading_title'] = $this->language->get('heading_title');
			$data['breadcrumbs'][] = array(
				'text' => $this->language->get('text_home'),
				'href' => $this->url->link('common/dashboard', 'token=' . $this->session->data['token'], 'SSL')
			);
			$data['breadcrumbs'][] = array(
				'text' => $this->language->get('heading_title'),
				'href' => $this->url->link('stock/money_out', 'token=' . $this->session->data['token'] . $url, 'SSL')
			);
			$data['enable_action'] = $this->url->link('stock/money_out/payall', 'token=' . $this->session->data['token'] . $url, 'SSL');
			/*面包屑导航栏*/
			
			/*右上操作按钮*/

		$data['button_add'] = $this->language->get('button_add');
		$data['buttons_enable'] = $this->language->get('buttons_enable');
		$data['buttons_disable'] = $this->language->get('buttons_disable');
		$data['text_confirm_enable'] = $this->language->get('text_confirm_enable');
		$data['text_confirm_disable'] = $this->language->get('text_confirm_disable');
		
		$data['add'] = $this->url->link('stock/money_out/add', 'token=' . $this->session->data['token'] . $url, 'SSL');
		/*右上操作按钮*/
		

		/*标题*/
		$data['text_list'] = $this->language->get('text_list');
		/*标题*/
		    // var_dump($data['refer_type_id']);
		//加载头部侧边栏底部
		$data['header'] = $this->load->controller('common/header');
		$data['column_left'] = $this->load->controller('common/column_left');
		$data['footer'] = $this->load->controller('common/footer');
		$this->response->setOutput($this->load->view('stock/money_out_list.tpl', $data));

	}

 	public function add() {
		$this->load->model('stock/money_in');
		$this->load->model('stock/money_out');
	    $this->document->addScript('view/javascript/angular.js');
	    $this->document->addScript('view/javascript/stock.js');
	    $this->getForm();
	}

	protected function getForm(){
		$data['breadcrumbs'] = array();

		$data['breadcrumbs'][] = array(
		  'text' => $this->language->get('text_home'),
		  'href' => $this->url->link('common/dashboard', 'token=' . $this->session->data['token'], 'SSL')
		);

		$data['breadcrumbs'][] = array(
		  'text' => '应付款项',
		  'href' => $this->url->link('stock/money_out', 'token=' . $this->session->data['token'] , 'SSL')
		);
		$this->document->addScript('view/javascript/angular.js');
    	$this->document->addScript('view/javascript/stock.js');
		$data['heading_title'] = '应付款项';
		$data['status'] = $this->model_stock_money_in->getstatus();
		$data['money_type'] = $this->model_stock_money_in->getMoneyType(2);


		$data['token'] = $this->session->data['token'];

		$data['cancel'] = $this->url->link('stock/money_out', 'token=' . $this->session->data['token'] , 'SSL');
		$data['header'] = $this->load->controller('common/header');
		$data['column_left'] = $this->load->controller('common/column_left');
		$data['footer'] = $this->load->controller('common/footer');
    	$this->response->setOutput($this->load->view('stock/money_out_add.tpl', $data));
	}

	public function save()
	{
		$this->load->model('stock/stock_in');
		$data = json_decode(file_get_contents('php://input'), true);
		$filter = C('DEFAULT_FILTER');
		$data   =   is_array($data) ? array_map_recursive($filter,$data) : $filter($data);
		// var_dump($data);
		$this->load->model('stock/money_out');
	 	$this->model_stock_money_out->addout($data,$this->user->getId(), $this->user->getUserName());

		$this->response->addHeader('Content-Type: application/json');
		$this->response->setOutput(json_encode(array('success'=>true)));

    }

    public function cancel()
	{
		$this->load->model('stock/money_out');
		$json = array();
		$money_out_id = I('get.money_out_id');
    	if (empty($money_out_id)) {
	    	$json['error'] = '参数缺失';
    	}else{
 			if ($this->model_stock_money_out->cancel($money_out_id)) {
 				$comments = "作废应付" ; 
      			$this->model_stock_money_out->addMoHistory($money_out_id, $this->user->getId(),$comments);
 				$json['success'] = '作废成功';
 			}else{
 				$json['success'] = '作废失败';
 			}
	    	
	    }

	    $this->response->addHeader('Content-Type: application/json');
	    $this->response->setOutput(json_encode($json));
    
	}

	public function recover()
	{
		$this->load->model('stock/money_out');
		$json = array();
		$money_out_id = I('get.money_out_id');
    	if (empty($money_out_id)) {
	    	$json['error'] = '参数缺失';
    	}else{
 			if ($this->model_stock_money_out->recover($money_out_id)) {
 				$comments = "取消作废" ; 
      			$this->model_stock_money_out->addMoHistory($money_out_id, $this->user->getId(),$comments);
 				$json['success'] = '作废成功';
 			}else{
 				$json['success'] = '作废失败';
 			}
	    	
	    }

	    $this->response->addHeader('Content-Type: application/json');
	    $this->response->setOutput(json_encode($json));
    
	}

	public function pay()
	{
		$this->load->model('stock/money_out');
		$money_out_id = I('get.money_out_id');
    	if (empty($money_out_id)) {
	    	$json['error'] = '参数缺失';
    	}else{
 			if ($this->model_stock_money_out->pay($money_out_id)) {
 				$comments = "确认支付" ; 
      			$this->model_stock_money_out->addMoHistory($money_out_id, $this->user->getId(),$comments);
 				$json['success'] = '作废成功';
 			}else{
 				$json['success'] = '作废失败';
 			}
	    	
	    }

    	$this->index();
	}

	public function edit()
	{
		$this->load->model('stock/money_out');
		$this->load->model('stock/money_in');
	    $this->document->addScript('view/javascript/angular.js');
	    $this->document->addScript('view/javascript/stock.js');
	    $this->form();
    
	}

	public function form()
	{
		$data['breadcrumbs'] = array();

		$data['breadcrumbs'][] = array(
		  'text' => $this->language->get('text_home'),
		  'href' => $this->url->link('common/dashboard', 'token=' . $this->session->data['token'], 'SSL')
		);

		$data['breadcrumbs'][] = array(
		  'text' => '应付款项',
		  'href' => $this->url->link('stock/money_out', 'token=' . $this->session->data['token'] , 'SSL')
		);
		$this->document->addScript('view/javascript/angular.js');
    	$this->document->addScript('view/javascript/stock.js');
		$data['heading_title'] = '应付款项';
		$money_out_id = I('get.money_out_id');
		$data['money_out'] = $this->model_stock_money_out->getMoById($money_out_id);
		$data['status'] = $this->model_stock_money_in->getstatus();
		$data['money_type'] = $this->model_stock_money_in->getMoneyType(2);


		$data['token'] = $this->session->data['token'];

		$data['cancel'] = $this->url->link('stock/money_out', 'token=' . $this->session->data['token'] , 'SSL');
		$data['header'] = $this->load->controller('common/header');
		$data['column_left'] = $this->load->controller('common/column_left');
		$data['footer'] = $this->load->controller('common/footer');
    	$this->response->setOutput($this->load->view('stock/money_out_edit.tpl', $data));
    
	}

	public function edit_out()
	{
		$data = json_decode(file_get_contents('php://input'), true);
		$filter = C('DEFAULT_FILTER');
		$data   =   is_array($data) ? array_map_recursive($filter,$data) : $filter($data);
		$this->load->model('stock/money_out');
		$money_out_id = I('get.money_out_id');
	 	$this->model_stock_money_out->editout($data,$money_out_id);
	 	$comments = "增加实付金额:".$data['paidadd'];
	 	$this->model_stock_money_out->addMoHistory($money_out_id, $this->user->getId(),$comments);
		$this->response->addHeader('Content-Type: application/json');
		$this->response->setOutput(json_encode(array('success'=>true)));

    }

    public function view()
	{
		$this->load->model('stock/money_out');
	    $this->document->addScript('view/javascript/angular.js');
	    $this->document->addScript('view/javascript/stock.js');
		$data['breadcrumbs'] = array();

		$data['breadcrumbs'][] = array(
		  'text' => $this->language->get('text_home'),
		  'href' => $this->url->link('common/dashboard', 'token=' . $this->session->data['token'], 'SSL')
		);

		$data['breadcrumbs'][] = array(
		  'text' => '应付款项',
		  'href' => $this->url->link('stock/money_out', 'token=' . $this->session->data['token'] , 'SSL')
		);
		$this->document->addScript('view/javascript/angular.js');
    	$this->document->addScript('view/javascript/stock.js');
		$data['heading_title'] = '应付款项';
		$money_out_id = I('get.money_out_id');
		$data['money_out'] = $this->model_stock_money_out->getMoById($money_out_id);
		$data['stock_in'] = $this->model_stock_money_out->getOutById($data['money_out']);
    	$data['histories'] =  $this->model_stock_money_out->getHistory($money_out_id);

		$data['token'] = $this->session->data['token'];

		$data['cancel'] = $this->url->link('stock/money_out', 'token=' . $this->session->data['token'] , 'SSL');
		$data['header'] = $this->load->controller('common/header');
		$data['column_left'] = $this->load->controller('common/column_left');
		$data['footer'] = $this->load->controller('common/footer');
    	$this->response->setOutput($this->load->view('stock/money_out_list_view.tpl', $data));

    }
    public function searchOrder() {
		
		$json = array();

		if (isset($this->request->get['filter_name'])) {
			$filter_name = trim($this->request->get['filter_name']);
		}
		else{
			$filter_name = '';
		}
		if (isset($this->request->get['money_type'])) {
			$money_type = trim($this->request->get['money_type']);
		}
		if ($money_type==2) {
			$this->load->model('stock/money_out');
			$filter_data['id'] = array('like',"%".$filter_name."%");
			$results = $this->model_stock_money_out->getAutoCompletePo($filter_data);
			foreach ($results as $result){
				$json[] = array(
					'refer_id' => $result['id'],
					'payee' => $result['company'],
					'payee_id' => $result['vendor_id'],
					'total' => (float)$result['total'],
					'payables' => (float)$result['payables'],

				);
			}
		}elseif ($money_type==3) {
			$this->load->model('stock/money_out');
			$filter_data['id'] = array('like',"%".$filter_name."%");
			$results = $this->model_stock_money_out->getAutoCompleteRe($filter_data);
			foreach ($results as $result){
				$json[] = array(
					'refer_id' => $result['id'],
					'payee' => $result['payment_company'],
					'payee_id' => $result['customer_id'],
					'total' =>(float) $result['total'],
					'payables' => (float)$result['payables'],

				);
			}
		}else{
			$json[] = array(
				'error'=>'请选择相关数据类型'
				);
		}


		

		// var_dump($results);
		
		// var_dump($json);
		$this->response->addHeader('Content-Type: application/json');
		$this->response->setOutput(json_encode($json));
	}

	public function test(){
		$this->load->model('stock/money_out');

		$res = $this->model_stock_money_out->getIn();
		foreach ($res as $key => $value) {
			$data = array(
					'refer_total'=>$value['total'], 
					'payables'=>$value['stin'], 
		  			'refer_id'=>$value['id'],
		  		 	'refer_type_id'=>2, 
		  		 	'payee'=>$value['company'], 
		  		 	'payee_id'=>$value['vendor_id'], 
		  		 	'status'=>1, 
					'trader_type'=>1, 
					'comment'=>'系统批量导入', 
					'date_added'=>date('Y-m-d H:i:s'));
			M('money_out')->data($data)->add();
		}
		$res1 = $this->model_stock_money_out->getRo();

		foreach ($res1 as $key1 => $value1) {
			$data = array(
					'refer_total'=>$value1['buy_money'], 
					'payables'=>$value1['buy_money'], 
		  			'refer_id'=>$value1['id'],
		  		 	'refer_type_id'=>3, 
		  		 	'payee'=>$value1['company_name'].'('.$value1['telephone'].')', 
		  		 	'payee_id'=>$value1['customer_id'], 
		  		 	'status'=>1, 
					'trader_type'=>2, 
					'comment'=>'系统批量导入', 
					'date_added'=>date('Y-m-d H:i:s'));
			M('money_out')->data($data)->add();
		}
	}

	// public function payall(){
		
	// 	$this->load->language('stock/money_out');
	// 	$this->document->setTitle($this->language->get('heading_title'));
	// 	$this->load->model('stock/money_out');
	// 	$this->load->model('stock/money_in');
	// 	$paid  = $this->request->post['paid'];
	// 	$selected = $this->request->post['selected'];
	// 	var_dump($paid);
	// 	var_dump($selected);
	// 	foreach ($selected as $key => $value) {
			
	// 	}
	// }
	function validateDate($date, $format = 'Y-m-d H:i:s')
  {
      $d = DateTime::createFromFormat($format, $date);
      return $d && $d->format($format) == $date;
  }

	

	public function genNewVendorBill(){
		//判断日期的合理性
	    $vendor_bill_month = I('get.year_month');
	    $month_valid = $this->validateDate($vendor_bill_month, 'Y-m');
	    if(!$month_valid) {
	      $this->session->data['error'] = '请选择有效的年月日期';
	      $this->response->redirect($this->url->link('sale/order', 'token=' . $this->session->data['token'], 'SSL'));
	    }
	    //获取选择月份之前半年内所有还未对账的完成状态的订单
	    $d = DateTime::createFromFormat('Y-m', $vendor_bill_month);
	    $cur_year_month = $d->format('Y-m-01');
	    $cur_month = $d->format('Y-m-21');
	    
	    $d->modify('-12 month');
	    $end_month = $d->format('Y-m-d');

	    $this->load->model('stock/money_out');

		$billed_money_out = $this->model_stock_money_out->getBillMoney($cur_month, $end_month);
	    // $this->load->model('sale/vendor_bill');
	    // $this->load->model('sale/purchase_order');
	    // $billed_po = $this->model_sale_vendor_bill->getBilledPo($cur_month, $end_month);
	    $billed_money_out_ids = array();
	    foreach ($billed_money_out as $out) {
	      $billed_money_out_ids[] = $out['money_out_id'];
	    }

	    // $vendor_ids = $this->model_sale_purchase_order->getVendorIdsFromPo($cur_month, $end_month, $billed_money_out_ids);
	    $vendor_ids = $this->model_stock_money_out->getVendorIdsFromMoney($cur_month, $end_month, $billed_money_out_ids);
	    // var_dump($vendor_ids);die();
	    foreach ($vendor_ids as $vendor_id) {
	      $bill_data = $this->model_stock_money_out->getToByVendeyMoney($cur_month, $end_month, $billed_money_out_ids, $vendor_id['payee_id']);  

	      $bill_data_full[$vendor_id['payee_id']] = $bill_data;
	    }
	    //估计是税率@moshan
	    $commission = 1;
	
	    foreach ($bill_data_full as $vendor_id=>$bill_data) {
	      $payables_total = 0;
	      $payables_dtotal = 0;
	      $monery_ids = array();
	      foreach ($bill_data as $po) {
	      	//防止应付表里的应付数据有问题，获取详情加以判断
	      	$payables_dtotal += $this->model_stock_money_out->getdetailtotal($po['stock_in_ids']);
			$payables_total += (float)$po['payables'];
	        $monery_ids[] = $po['money_out_id'];
	      }
	      // var_dump($payables_dtotal);
	      // var_dump($payables_total);
	      $data = array();


	      //此处的$vendor_id是money_out表中的vendor_id 对应的是 vendors表中的user_id
	      $this->load->model('catalog/vendor');
	      $vendor_info = $this->model_catalog_vendor->getVendorByUserId($vendor_id);
     	  $data['vendor_id'] = $vendor_info['vendor_id'];
	      $data['year_month'] = $cur_year_month;
	      //如果数据不一致加备注
	      if ($payables_total!=$payables_dtotal) {
	      	$data['comment'] = '详情计算总和'.$payables_dtotal.'。应付总和'.$payables_total;
	      }
	      $commission_total = $payables_dtotal*$commission;
	      $data['total'] = $commission_total;
	      $data['payables_total'] = $payables_dtotal;
	      $data['date_added'] = date('Y-m-d H:i:s', time());
	      $vendor_bill_id = $this->model_stock_money_out->saveVendorBill($data, $monery_ids);

	      //添加生成po操作记录
	      $this->load->model('user/user');
	      $user_info = $this->model_user_user->getUser($this->user->getId());
	      $comment = '新增品牌商对账单';
	      $this->model_stock_money_out->addVendorBillHistory($vendor_bill_id, $this->user->getId(), $user_info['fullname'], $comment);      
	    }
	    
	    // var_dump($bill_data_full);die();

	    $this->session->data['success'] = '生成品牌厂商对账单成功';
	    $this->response->redirect($this->url->link('stock/factory', 'token=' . $this->session->data['token'], 'SSL'));
	}

	 public function addIds(){
		  	$sql = "SELECT `refer_id` FROM `money_out` WHERE `trader_type` = 1 AND `refer_type_id` = 2";
		  	$query = $this->db->query($sql);
		  	$i=0;
		  	foreach ($query->rows as $key => $value) {
		  		$sql = "SELECT stock_in.in_id from stock_in WHERE refer_id=".$value['refer_id']." AND refer_type_id =2";
		  		$query = $this->db->query($sql);
		  		$in_id = $query->rows;
		  		$str ='';
		  		foreach ($in_id as $key1 => $value1) {
		  			$str .=','.$value1['in_id'];
		  		}
		  		// var_dump($str);
		  		$sql = "update money_out  set stock_in_ids='".$str."'WHERE refer_id=".$value['refer_id']." AND refer_type_id =2";
		  		$this->db->query($sql);
		  	}
		  	
	}

	public function upPo(){
		  	$sql = "SELECT SUM(po_product.delivered_qty* po_product.unit_price) AS 'total', po_product.po_id FROM po_product LEFT JOIN po ON po.id = po_product.po_id WHERE (po.`status`=4 OR po.`status` =6) AND po.date_added>20180101 GROUP BY po_product.po_id ";
		  	$query = $this->db->query($sql);
		  	foreach ($query->rows as $key => $value) {
		  		// var_dump($query->rows);die();
		  		$sql = "update po  set total ='".$value['total']."'WHERE id=".$value['po_id'];
		  		$this->db->query($sql);
		  	}
		  	
	}

	public function upPopcode(){
		  	$sql = "SELECT SUM(po_product.delivered_qty* po_product.unit_price) AS 'total', po_product.po_id FROM po_product LEFT JOIN po ON po.id = po_product.po_id WHERE (po.`status`=4 OR po.`status` =6) AND po.date_added>20170801 GROUP BY po_product.po_id ";
		  	$query = $this->db->query($sql);
		  	foreach ($query->rows as $key => $value) {
		  		// var_dump($query->rows);die();
		  		$sql = "update po  set total ='".$value['total']."'WHERE id=".$value['po_id'];
		  		$this->db->query($sql);
		  	}
		  	
	}
	//修改商品售价
	public function upProsale(){
		  	$sql = " SELECT product.product_id,product.product_type FROM product";
		  	$query = $this->db->query($sql);
		  	foreach ($query->rows as $key => $value) {
		  		if ($value['product_type']==1) {
		  					  		// var_dump($value);
			  		$sql = "SELECT price FROM product_special ps WHERE ps.product_id = ".$value['product_id']." AND ps.customer_group_id = '1'AND ((ps.date_start = '0000-00-00' ) AND (ps.date_end = '0000-00-00') ) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1";

			  		// echo $sql;
			  		$query = $this->db->query($sql);
			  		$price = $query->row['price'];
			  		if (empty($price)) {

			  			$sql = "SELECT price FROM product_special ps WHERE ps.product_id = ".$value['product_id']." AND ps.customer_group_id = '1' ORDER BY ps.priority ASC, ps.price ASC LIMIT 1";

				  		// echo $sql;
				  		$query = $this->db->query($sql);
				  		$price = $query->row['price'];


					}
		  		}elseif ($value['product_type']==2) {
		  			$sql = "SELECT price FROM product_special ps WHERE ps.product_id = ".$value['product_id']." AND ps.customer_group_id = '1'AND ((ps.date_start = '0000-00-00' ) AND (ps.date_end = '0000-00-00') ) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1";

			  		// echo $sql;
			  		$query = $this->db->query($sql);
			  		$price = $query->row['price'];
			  		if (empty($price)) {
			  			$sql = " SELECT product.price FROM product WHERE product_id = ".$value['product_id'];
			  			$query = $this->db->query($sql);
			  			$price = $query->row['price'];
			  		}

		  		}

		  		$sql = "update product set sale_price = ".$price." WHERE product_id = ".$value['product_id'];
		  		$this->db->query($sql);
		  	}
		  	
	}



	public function exportlists(){
		$a =time();

		if (isset($this->request->get['filter_name'])) {
			$filter_name = $this->request->get['filter_name'];
			$filter_data['pd.name'] = array('like','%'.$filter_name.'%');
		} else {
			$filter_name = null;
		}

		if (isset($this->request->get['filter_model'])) {
			$filter_model = $this->request->get['filter_model'];
			$filter_data['p.model'] = array('like','%'.$filter_model.'%');
		} else {
			$filter_model = null;
		}

		if (isset($this->request->get['filter_price'])) {
			$filter_price = $this->request->get['filter_price'];
			$filter_data['p.price'] = $filter_price;
		} else {
			$filter_price = null;
		}

		if (isset($this->request->get['filter_quantity'])) {
			$filter_quantity = $this->request->get['filter_quantity'];
			$filter_data['p.quantity'] = trim($filter_quantity);
		} else {
			$filter_quantity = null;
		}

		if (isset($this->request->get['filter_status'])) {
			$filter_status = $this->request->get['filter_status'];
			$filter_data['p.status'] = $filter_status;
		} else {
			$filter_status = null;
		}
		
		//mvds
		if (isset($this->request->get['filter_sku'])) {
			$filter_sku = $this->request->get['filter_sku'];
			$filter_data['p.sku'] = array('like','%'.$filter_sku.'%');
		} else {
			$filter_sku = null;
		}
		
		//商品编码
		if (isset($this->request->get['filter_product_code'])) {
			$filter_product_code = $this->request->get['filter_product_code'];
			$filter_data['p.product_code'] = array('like','%'.$filter_product_code.'%');
		} else {
			$filter_product_code = null;
		}
			
		if (isset($this->request->get['filter_vendor'])) {
			$filter_vendor = $this->request->get['filter_vendor'];
		} else {
			$filter_vendor = NULL;
		}
			
		if (isset($this->request->get['filter_vendor_name'])) {
			$filter_vendor_name = $this->request->get['filter_vendor_name'];
			$filter_data['vs.vendor_id'] = $filter_vendor_name;
		} else {
			$filter_vendor_name = NULL;
		}
		$this->load->model('catalog/mvd_product');
		$allProduct_data = $this->model_catalog_mvd_product->getAllProductsDetail($filter_data);
		$status_array = array(
			'1' => '启用',
			'0'	=> '停用',
			'5' => '待审批',
			'3' => '初始',
			);
        $this->load->model('stock/stocksearch');
        $this->load->model('sale/order');
        $reportInfo = $this->model_sale_order->getGatherSaleReport($this->model_stock_stocksearch->getGatherQty());
        $BuyInfo = $this->model_stock_stocksearch->getGatherBuyQtyBy();
        //获取平均进货价
        $Buyagvpric = $this->model_stock_stocksearch->getAgvBuy();
        // var_dump($Buyagvpric);die();
        $BuyInfo = $this->model_stock_stocksearch->getGatherBuyQtyBy();
        $dcycle = $this->model_stock_stocksearch->getDeliveryCycleall();
        $saleqty = $this->model_stock_stocksearch->getAllsaleqty();
        // var_dump($saleqty);

		foreach ($allProduct_data as $key => $value) {
			// var_dump($dcycle[$value['product_code']]);
            if ($value['product_id'] < 1){//屏蔽 product_id=0 的脏数据
                continue;
            }
            $isSingle = (1 == $value['is_single'])? 'A' : 'B' ;
			$save_data[$key] = array(
				'product_id' 	=> $value['product_id'],	
				'maname' 	=> $value['maname'],	
				'lastname'		=> $value['lastname'],
				'vendor_name'	=> $value['vendor_name'],
				'product_code' => $value['product_code'],
				'mname'			=> $value['mname'],
				'name'          => $value['name'],
				'image'          => $value['image'],
				'p_type' => $value['p_type'],
				'sku'			=> $value['sku'],
				'product_class1' =>$value['product_class1'],
				'product_class2' =>$value['product_class2'],
				'product_class3' =>$value['product_class3'],
				'model'			=> $value['model'],
				'packing_no'	=> $value['packing_no'],
				'middle_package'	=> $value['middle_package'],
				'minimum'		=> $value['minimum'],
				'product_cost'	=> $value['product_cost'],
				'price'			=> $value['price'],
				'sale_price'			=> $value['sale_price'],
				'date_added'	=> $value['date_added'],
				'status'		=> $status_array[$value['status']],
				'item_no'		=> $value['item_no'],
				'min_purchase_no'=>$value['min_purchase_no'],
				'quantity'		=> $value['quantity'],
				'length'		=> $value['length'],
				'height'		=> $value['height'],
				'width'			=> $value['width'],
				'weight'		=> $value['weight'],
				'sort_order'	=> $value['sort_order'],
				'ovd_name'      => $value['ovd_name'],
				'cd_name'		=> $value['cd_name'],
                'is_single'     => $isSingle,
                'TQLW'          => $reportInfo[$value['product_code']]['TQLW'],
                'TPLW'          => $reportInfo[$value['product_code']]['TPLW'],
                'TQLM'          => $reportInfo[$value['product_code']]['TQLM'],
                'TPLM'          => $reportInfo[$value['product_code']]['TPLM'],
                'TQTY'          => $reportInfo[$value['product_code']]['TQTY'],
                'TPTY'          => $reportInfo[$value['product_code']]['TPTY'],
                'GPRTY'         => $reportInfo[$value['product_code']]['GPRTY'],
                'SQN'           => $reportInfo[$value['product_code']]['SQN'],
                'SCN'           => ($reportInfo[$value['product_code']]['SQN']*$value['product_cost']),
                'SSC'           => $reportInfo[$value['product_code']]['SSC'],
                'SIOW'          => $reportInfo[$value['product_code']]['SIOW'],
                'SIOWSC'        => $reportInfo[$value['product_code']]['SIOWSC'],
                'SAFE'        => $reportInfo[$value['product_code']]['SAFE'],
                'BUYQTY'        => $BuyInfo[$value['product_code']]['buyqty'],
                'BUYPRICE'        => $BuyInfo[$value['product_code']]['buyprice'],
                'AVC'        => $dcycle[$value['product_code']]['av'],
                'SALEQTY'        => $saleqty[$value['product_code']]['quantity'],
                'clearance'     => $value['clearance'],

            );
            //如果有平均进货价取平均，没有取进货价
			$product_cost = $Buyagvpric[$value['product_code']]['buyav'];
			$product_costn = floatval($value['product_cost']);
			if (empty($product_cost)) {
				$product_cost = floatval($value['product_cost']);
			}
			
			$special = $value['sale_price'];

			$product_specials = $this->model_catalog_mvd_product->getProductSpecials($value['product_id']);

			foreach ($product_specials  as $product_special) {
				if (($product_special['date_start'] == '0000-00-00' || strtotime($product_special['date_start']) < time()) && ($product_special['date_end'] == '0000-00-00' || strtotime($product_special['date_end']) > time())) {
				
					$special = $product_special['price'];

					break;
				}
			}
			$save_data[$key]['special'] =$special;
			$save_data[$key]['profit'] = (floatval(intval(($special-$product_cost)/$special*10000))/100).'%  '; //累计毛利率
			// var_dump($special);
			// var_dump($product_costn); die();
			$save_data[$key]['profitn'] = (floatval(intval(($special-$product_costn)/$special*10000))/100).'%  '; //毛利率
			$save_data[$key]['super_profit'] = (floatval(intval(($save_data[$key]['price']-$product_cost)/$save_data[$key]['price']*10000))/100).'%  '; //超市毛利率
		}

		      //   import("Org.Util.PHPExcel");
        // import("Org.Util.PHPExcel.Worksheet.Drawing");
        // import("Org.Util.PHPExcel.Writer.Excel2007");
		$this->load->library('PHPExcel/PHPExcel');
		$this->load->library('PHPExcel/PHPExcel/Worksheet/Drawing');
        // var_dump($objDrawing);die();
		$objPHPExcel = new PHPExcel();    
		$objProps = $objPHPExcel->getProperties();    
		$objProps->setCreator("Think-tec");
		$objProps->setLastModifiedBy("Think-tec");    
		$objProps->setTitle("Think-tec Contact");    
		$objProps->setSubject("Think-tec Contact Data");    
		$objProps->setDescription("Think-tec Contact Data");    
		$objProps->setKeywords("Think-tec Contact");    
		$objProps->setCategory("Think-tec");
		$objPHPExcel->setActiveSheetIndex(0);     
		$objActSheet = $objPHPExcel->getActiveSheet(); 
		
		$sheet2 = new PHPExcel_Worksheet($objPHPExcel, '带库存');
		$objPHPExcel->addSheet($sheet2);
		$objPHPExcel->setActiveSheetIndex(1);
		$objActSheet = $objPHPExcel->getActiveSheet();
		$col_idx = 'A';

		$headers = array('商品图片','商品ID','商品状态','厂商编号','厂商名称','品牌','分类','大分类','中分类','小分类','商品名称','商品编码','货号','可卖量（前台库存）','可用库存（后台库存）','成本', '目前售价','商品售价','零售参考价','累计毛利率','毛利率','条形码','长','宽','高','重量(kg)','箱入数','最小进货数','最小起订量','排序编号','上架时间','是否单卖（A/B类商品）','上周销量','上周销售额','上四周销量','上四周销售额','年至今销量','年至今销售额','年至今毛利','库存数量','安全库存','库存成本','库存预计销售周期（天）','在途订量','订购数量','是否为清仓商品','用户购买量','订购金额','在途数量预计销售周期（周）');
		$row_keys = array('product_id','status','lastname','vendor_name','maname','cd_name','product_class1','product_class2','product_class3','name', 'product_code','item_no','quantity','SQN','product_cost','special','sale_price','price','profit','profitn','sku','length','width','height','weight','packing_no','min_purchase_no','minimum','sort_order','date_added','is_single','TQLW','TPLW','TQLM','TPLM','TQTY','TPTY','GPRTY','SQN','SAFE','SCN','SSC','SIOW','BUYQTY','clearance','SALEQTY','BUYPRICE','SIOWSC');

		foreach ($headers as $header) {
			$objActSheet->setCellValue($col_idx++.'1', $header);
			// $objPHPExcel->getActiveSheet()->getColumnDimension($col_idx)->setHeight(80);	

		}

		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);	

        // $objActSheet

		//添加物流信息
		$i = 2;
		// var_dump($save_data);
		foreach ($save_data as $rlst) {
			$url='http://'.$_SERVER['SERVER_NAME'].$_SERVER["REQUEST_URI"]; 
			// echo dirname($url);
			$objDrawing[$i] = new \PHPExcel_Worksheet_Drawing();
			// var_dump($objDrawing[$i]);
			$image = $rlst['image'];
			$dir = dirname($image);
			$extension = pathinfo($image, PATHINFO_EXTENSION);
			$url = '/usr/share/nginx/html/bhz/image/cache/'.$dir.'/'.$rlst['product_code'].'.'.$extension;
			// if (file_exists($url)) {
			// 	$objDrawing[$i]->setPath($url,1);
	  //           // 设置宽度高度
	  //           $objDrawing[$i]->setHeight(80);//照片高度
	  //           $objDrawing[$i]->setWidth(80); //照片宽度
	  //           /*设置图片要插入的单元格*/
	  //           $objDrawing[$i]->setCoordinates('A'.$i);
	  //           // 图片偏移距离
	  //           $objDrawing[$i]->setOffsetX(0);
	  //           $objDrawing[$i]->setOffsetY(0);
	  //           $objDrawing[$i]->setWorksheet($objPHPExcel->getActiveSheet());
			// }
			// var_dump($url);
			// $url  = iconv('utf-8', 'gb2312', $url);
			// var_dump($url);die();
			if (file_exists($url)) {
				$objDrawing[$i]->setPath($url,1);
	            // 设置宽度高度
	            $objDrawing[$i]->setHeight(80);//照片高度
	            $objDrawing[$i]->setWidth(80); //照片宽度
	            /*设置图片要插入的单元格*/
	            $objDrawing[$i]->setCoordinates('A'.$i);
	            // 图片偏移距离
	            $objDrawing[$i]->setOffsetX(0);
	            $objDrawing[$i]->setOffsetY(0);
	            $objDrawing[$i]->setWorksheet($objPHPExcel->getActiveSheet());
			}
            

			$col_idx = 'B';
			foreach ($row_keys as $rk) {
				// $objActSheet->setCellValue($col_idx++.$i, $rlst[$rk]);	
				$objActSheet->setCellValueExplicit($col_idx++.$i, $rlst[$rk], PHPExcel_Cell_DataType::TYPE_STRING);
			}

			$objActSheet->getRowDimension($i)->setRowHeight(80);
			$i++;
		}

		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		ob_end_clean();
		// Redirect output to a client’s web browser (Excel2007)
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header('Content-Disposition: attachment;filename="Products_'.date('Y-m-d',time()).'.xlsx"');
		header('Cache-Control: max-age=0');
		// If you're serving to IE 9, then the following may be needed
		header('Cache-Control: max-age=1');

		// If you're serving to IE over SSL, then the following may be needed
		header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
		header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
		header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
		header ('Pragma: public'); // HTTP/1.0
		 
    $objWriter->save('php://output'); 

    exit;
	}


	public function look_down(){
		$product_ids = M('product')->field('product_code,image')->where(array('status' => 3 ))->select();
		// var_dump($product_ids);
		$this->load->model('catalog/mvd_product');
		$this->load->model('tool/image');
		$i = 0;
		foreach ($product_ids  as $key => $value) {
			$thumb = $value['image'];
			// var_dump($thumb);
			$thumb = $this->resize($thumb, 100, 100,$value['product_code']);
			// var_dump($thumb);
			$thumb  = iconv('utf-8', 'gb2312', $thumb);
			// var_dump($thumb);
			// sleep(2);
			// die();
			$i++;
			if ($i==10) {
				$i=1;
				// die();
			}
			// die();
		}

		die();

    }


   public function resize($filename, $width, $height,$product_code) {

		if (!is_file(DIR_IMAGE . $filename)) {
			return;
		}
		$dir = dirname($filename);
		$extension = pathinfo($filename, PATHINFO_EXTENSION);

		$old_image = $filename;
		$new_image = 'cache/' .$dir.'/'.$product_code.'.'.$extension;
		// var_dump($new_image);

		if (!is_file(DIR_IMAGE . $new_image) || (filectime(DIR_IMAGE . $old_image) > filectime(DIR_IMAGE . $new_image))) {
			$path = '';

			$directories = explode('/', dirname(str_replace('../', '', $new_image)));

			foreach ($directories as $directory) {
				$path = $path . '/' . $directory;

				if (!is_dir(DIR_IMAGE . $path)) {
					@mkdir(DIR_IMAGE . $path, 0777);
				}
			}

			list($width_orig, $height_orig) = getimagesize(DIR_IMAGE . $old_image);

			if ($width_orig != $width || $height_orig != $height) {
				$image = new Image(DIR_IMAGE . $old_image);
				$image->resize($width, $height);
				$image->save(DIR_IMAGE . $new_image);
			} else {
				copy(DIR_IMAGE . $old_image, DIR_IMAGE . $new_image);
			}
		}

		if ($this->request->server['HTTPS']) {
			return HTTPS_CATALOG . 'image/' . $new_image;
		} else {
			return HTTP_CATALOG . 'image/' . $new_image;
		}
	}
	//经纬度
	public function getlng(){
		$sql = "SELECT a.address, a.address_id, a.customer_id, a.fullname, a.company, a.postcode, a.country_id, (SELECT city.`name` FROM `city` WHERE city.city_id = a.city_id ) AS 'city', (SELECT zone.`name` FROM `zone` WHERE zone.zone_id = a.zone_id ) AS 'zone', (SELECT country.`name` FROM `country` WHERE country.country_id = a.country_id ) AS 'country', a.zone_id, a.custom_field, a.shipping_telephone, a.city_id, a.need_review, a.lng, a.lat FROM address AS a where a.lat=0 ";
		$query = $this->db->query($sql);
		$addressInfo = $query->rows;
		// $addressInfo = M('address')->where(array('lng'=>0))->select();
		$i = 0;
		foreach ($addressInfo as $key => $value) {


			// $sql = "update address set flag = 1 WHERE address_id=".$value['address_id'];
				$this->db->query($sql);
			$address =$value['country'].$value['zone'].$value['city'].$value['address'];
			if (!empty($value['address'])) {

				$str = 'http://apis.map.qq.com/ws/geocoder/v1/?address='.$address.'&output=json&key=WP4BZ-NFTAG-WEUQX-IMEIY-GVE2O-BYBYJ';
				// var_dump($str);die();
				$result = file_get_contents($str);
				$info = json_decode($result,true);

				if ($info['result']['location']['lng']!=0||$info['result']['location']['lat']!=0) {
					$sql = "update address set lng =".$info['result']['location']['lng'].", lat = ".$info['result']['location']['lat']."WHERE address_id=".$value['address_id'];
					$this->db->query($sql);
				}
				// if ($i==50) {
				// 	var_dump($address_id);die();
				// }
			
			}
			

		}
		echo $i;
	}


	public function insertInventory(){
		$sql = "SELECT op.order_id, op.product_code, op.quantity, op.price, op.product_type, op.product_id FROM `order_product` AS op WHERE exists (SELECT order_id FROM `order` AS o WHERE o.order_status_id <> 16 AND o.order_status_id <> 0 AND o.order_status_id <> 13 AND o.order_status_id <> 11 AND shelf_order_id!=0 ) AND op.product_type = 1 AND op.product_id is NOT null ";
		$query = $this->db->query($sql);
		$products1 = $query->rows;
		foreach ($products1 as $key => $value) {
			$order_id = $value['order_id'];
			$product_code = $value['product_code'];
			$productArr[$order_id][$product_code] = $value;
		}
		$sql = "SELECT op.order_id, op.product_type, opg.product_id, opg.product_code, opg.quantity, opg.price FROM order_product AS op LEFT JOIN order_product_group AS opg ON op.order_product_id = opg.order_product_id WHERE  exists ((SELECT order_id FROM `order` AS o WHERE o.order_status_id <> 16 AND o.order_status_id <> 0 AND o.order_status_id <> 13 AND o.order_status_id <> 11 AND shelf_order_id!=0)) AND op.product_type = 2";
		$query = $this->db->query($sql);
		$products2 = $query->rows;
		foreach ($products2 as $key => $value) {
			$order_id = $value['order_id'];
			$product_code = $value['product_code'];
			$productArr[$order_id][$product_code] = $value;
		}
		$sql = "SELECT o.order_id, o.customer_id FROM `order` AS o WHERE o.order_status_id <> 16 AND o.order_status_id <> 0 AND o.order_status_id <> 13 AND o.order_status_id <> 11";
		$query = $this->db->query($sql);
		$customerOrder = $query->rows;
		foreach ($customerOrder as $key => $value) {

			$customer_id = $value['customer_id'];
			$customerProductArr[$customer_id][] = $productArr[$value['order_id']];

		}
		$i=0;
		$keyArr = [];
		// var_dump($customerProductArr[4449]);
		foreach ($customerProductArr as $key1 => $value1) {
			foreach ($value1 as $key2 => $value2) {
				foreach ($value2 as $key => $value) {
					
						$csvInfo = array(
							'customer_id'=>$key1,
							'product_id'=>$value['product_id'],
							'product_code'=>$value['product_code'],
							'stock_qty'=>$value['quantity'],
							'date_added'=>date('Y-m-d H:i:s'),
							'date_modify'=>date('Y-m-d H:i:s'),
							'customer_sale_price'=>$value['price']
						);
						M('customer_inventory')->add($csvInfo);
					// }

				}
			}
		}
		echo $i;

	}


	public function insertInventormy(){
		$sql = "SELECT * FROM `product`";
		$query = $this->db->query($sql);
		$products2 = $query->rows;

		foreach ($products2 as $key => $value) {
					
			$csvInfo = array(
				'customer_id'=>4449,
				'shop_id'=>3,
				'product_id'=>$value['product_id'],
				'product_code'=>$value['product_code'].'0',
				'stock_qty'=>20,
				'date_added'=>date('Y-m-d H:i:s'),
				'date_modify'=>date('Y-m-d H:i:s'),
				'sale_price'=>$value['price'],
				'price'=>$value['price'],
			);
			M('shop_inventory')->add($csvInfo);
		// }

		}
			
		
		echo $i;

	}

	public function insertSolution(){
		$sql = "SELECT * FROM `customer_inventory`";
		$query = $this->db->query($sql);
		$products = $query->rows;
		foreach ($products as $key => $value) {
			$customer_id = $value['customer_id'];
			$arr[$customer_id ][] = $value;
		}
		foreach ($arr as $key => $value) {
			// var_dump($value);
			$data = array(
				'customer_id'=>$key,
				'solution_name'=>'大杂汇',
				'solution_category'=>855,
				'shelf_length'=>1,
				'is_template'=>1,
				'status'=>1,
				'date_added'=>date('Y-m-d H:i:s'),
			);
			$solution_id =  M('solution')->add($data);

			$data = array(
				'customer_id'=>$key,
				'solution_id'=>$solution_id,
				'shelf_name'=>1,
				'shelf_category'=>855,
				'status'=>1,
				'date_enter'=>date('Y-m-d H:i:s'),
				'date_added'=>date('Y-m-d H:i:s'),
			);
			$shelf_id =  M('shelf')->add($data);

			foreach ($value as $key1 => $value1) {

				$data = array(
					'shelf_id'=>$shelf_id,
					'product_id'=>$value1['product_id'],
					'product_code'=>$value1['product_code'],
					'show_qty'=>$value1['stock_qty'],
					'date_added'=>date('Y-m-d H:i:s'),
				);
				 M('shelf_product')->add($data);
				
			}
		}

	}

	public function upCustomer(){
		$sql = "SELECT * FROM `address` where lat<>0 GROUP BY customer_id";
		$query = $this->db->query($sql);
		$address = $query->rows;
		foreach ($address as $key => $value) {
			if (!empty($value['customer_id'])) {
				$data = array(
					'address'=>$value['address'],
					'country_id'=>$value['country_id'],
					'zone_id'=>$value['zone_id'],
					'city_id'=>$value['city_id'],
					'lng'=>$value['lng'],
					'lat'=>$value['lat'],
				);
				M('customer')->where(array('customer_id'=>$value['customer_id']))->save($data);
			}
			

		}
	}


	public function addPO(){
		  	$sql = "SELECT op.product_id, op.product_code, op.`name`, op.model, op.quantity, op.date_added, op.is_excel, v.vproduct_id, op.order_id, op.order_product_id, v.product_cost, op.product_type, v.vendor FROM order_product AS op LEFT JOIN vendor AS v ON op.product_id = v.vproduct_id WHERE op.is_excel = 1 AND op.date_added>'20180401000000' AND product_code <> '21010322330' AND product_code <> '21010322340'  ";
		  	$query = $this->db->query($sql);
		  	foreach ($query->rows as $key => $value) {
		  		$time = substr($value['date_added'],0,7);
		  		$vendor_id = $value['vendor'];
		  		$vkey = $time.','.$vendor_id;
		  		$pcode = $value['product_code'];
		  		$arr[$vkey][$pcode]['product_id'] =$value['product_id'];
		  		$arr[$vkey][$pcode]['product_code'] =$value['product_code'];
		  		$arr[$vkey][$pcode]['product_cost'] =$value['product_cost'];
		  		$arr[$vkey][$pcode]['name'] =$value['name'];
		  		$arr[$vkey][$pcode]['model'] =$value['model'];
		  		$arr[$vkey][$pcode]['qty'] +=$value['quantity'];
		  		$arr[$vkey][$pcode]['product_id'] =$value['product_id'];
		  		$arr[$vkey][$pcode]['vendor_id'] =$value['vendor'];
		  	}

			foreach ($arr as $key => $value) {
		     		$karr = explode(',',$key );
		     		// var_dump($karr);die();
		   
		     		$pdata = array(
		     			'user_id' => 160,
				        'date_added'=>$karr[0].'-01  11:32:22',
		     			'status' =>6 ,
		     			'included_order_ids'=>'导入订单导入',
		     			'vendor_id' =>$karr[1] ,
		     			'warehouse_id' =>10,
		     			 );
					$po_id = M('po')->add($pdata);
					$price=0;
		     		foreach ($value as $key1 => $value1) {
		     			// var_dump($value1);die();
		     			$product_data['po_id'] = $po_id;
						$product_data['product_id'] = $value1['product_id'];
						$product_data['qty'] = $value1['qty'];
						$product_data['delivered_qty'] = $value1['qty'];
						$product_data['unit_price'] = $value1['product_cost'];
						$product_data['price'] = $value1['qty']* $value1['product_cost'];
						$product_data['status'] = 6;
						$product_data['name'] = $value1['name'];
						$product_data['product_code'] = $value1['product_code'];
						$product_data['sku'] = $value1['sku'];
						$product_data['comment'] = $value1['comment'];
						M('po_product')->add($product_data);
						$price +=$product_data['qty']* $product_data['unit_price'];
						
						// echo M('stock_in_detail')->getLastsql();die();

					}
					M('po')->data(array('total'=>$price,'date_modified'=>$karr[0].'-01  11:32:22'))->where(array('id'=>$po_id))->save();	
			}
		  	
	}

	public function addstockOUt(){
		  	$sql = "SELECT op.product_id, op.product_code, op.`name`, op.model, op.quantity, op.date_added, op.is_excel, v.vproduct_id, op.order_id, op.order_product_id, v.product_cost, op.product_type, v.vendor FROM order_product AS op LEFT JOIN vendor AS v ON op.product_id = v.vproduct_id WHERE op.is_excel = 1 AND product_code <> '21010322330' AND product_code <> '21010322340' AND op.order_id IN (SELECT order_id FROM `order` WHERE `order`.store_url LIKE '%import%' AND  recommend_usr_id!=297) ";
		  	$query = $this->db->query($sql);
		  	foreach ($query->rows as $key => $value) {
		  		$time = substr($value['date_added'],0,7);
		  		$order_id = $value['order_id'];
		  		$vkey = $time.','.$order_id;
		  		$pcode = $value['product_code'];
		  		$arr[$vkey][$pcode]['product_id'] =$value['product_id'];
		  		$arr[$vkey][$pcode]['product_code'] =$value['product_code'];
		  		$arr[$vkey][$pcode]['product_cost'] =$value['product_cost'];
		  		$arr[$vkey][$pcode]['name'] =$value['name'];
		  		$arr[$vkey][$pcode]['model'] =$value['model'];
		  		$arr[$vkey][$pcode]['qty'] +=$value['quantity'];
		  		$arr[$vkey][$pcode]['product_id'] =$value['product_id'];
		  		$arr[$vkey][$pcode]['order_id'] =$value['order_id'];
		  	}
		  	// var_dump($arr);die();

			foreach ($arr as $key => $value) {
			    $karr = explode(',',$key );

				$sdata = array(
	     			'user_id' => 160,
	     			'refer_id' =>$karr[1],
	     			'refer_type_id' => 1,
			        'date_added'=>$karr[0].'-01  11:32:22',
	     			'status' =>2 ,
	     			'comment' =>'0424excel导入',
	     			'warehouse_id' =>10,
	     			 );
				$out_id = M('stock_out')->add($sdata);
				$price=0;
	     		foreach ($value as $key1 => $value1) {
	     			if (empty($value1['product_cost'])) {
	     				continue;
	     			}
	     			// var_dump($value1);die();
					$stdata = array(
						'product_code' => $value1['product_code'],
						'out_id' => $out_id,
						'product_quantity' => $value1['qty'] ,
						'product_price' => $value1['product_cost'],
						'products_money' => $value1['qty']* $value1['product_cost'],
						'pay_product_price' => $value1['product_cost'],
						'pay_products_money' => $value1['qty']* $value1['product_cost'],
						'date_added' => $karr[0].'-01  11:32:22',
					 );
					// var_dump($stdata);
					M('stock_out_detail')->add($stdata);
					$price +=$value1['qty']* $value1['product_cost'];

					// echo M('stock_in_detail')->getLastsql();die();

				}
				M('stock_out')->data(array('sale_money'=>$price))->where(array('out_id'=>$out_id))->save();	
				$shdata = array(
					'out_id' => $out_id, 
	     			'user_id' => 160,
					'operator_name' =>'永康物流营销部', 
					'comment' =>'excel导入',
					'date_added' => $karr[0].'-01  11:32:22', 
				);
				M('stock_out_history')->add($shdata);
	     		$this->addInvenou(10,$value,1,$out_id,$karr[0].'-01  11:32:22');
	     	}
		  	
	}

    public function getProvendor() {
    	$sql = "SELECT po.id, po.user_id, po.date_added, po.`status`, po.vendor_id, po.deliver_time, po.total, po.included_order_ids, po.warehouse_id, po_product.id, po_product.po_id, po_product.product_id, po_product.qty, po_product.delivered_qty, po_product.unit_price, po_product.price, po_product.`status`, po_product.`name`, po_product.sku, po_product.`comment`, po_product.sort_order, po_product.option_id, po_product.option_name, po_product.packing_no, po_product.product_code FROM po INNER JOIN po_product ON po.id = po_product.po_id WHERE po.included_order_ids LIKE '%导%'AND po.date_added>'2017090101000000'";
		  	$query = $this->db->query($sql);
		  	foreach ($query->rows as $key => $value) {
		  		$time = substr($value['date_added'],0,7);
		  		$po_id = $value['po_id'];
		  		$vkey = $time.','.$po_id;
		  		$pcode = $value['product_code'];
		  		if (!empty($pcode)) {
		  			$arr[$vkey][$pcode]['product_id'] =$value['product_id'];
			  		$arr[$vkey][$pcode]['product_code'] =$value['product_code'];
			  		$arr[$vkey][$pcode]['name'] =$value['name'];
			  		$arr[$vkey][$pcode]['qty'] =$value['delivered_qty'];
			  		$arr[$vkey][$pcode]['unit_price'] =$value['unit_price'];
			  		$arr[$vkey][$pcode]['date_added'] = $value['date_added'];
		  		}
		  		
		  	}
		  	// var_dump($arr);die();
     	foreach ($arr as $key => $value) {
		    $karr = explode(',',$key );

			$sdata = array(
     			'user_id' => 160,
     			'refer_id' =>$karr[1],
     			'refer_type_id' => 2,
		        'date_added'=>$karr[0].'-01  11:32:22',
     			'status' =>2 ,
     			'comment' =>'excel导入',
     			'warehouse_id' =>10,
     			 );
			$in_id = M('stock_in')->add($sdata);
			$price=0;
     		foreach ($value as $key1 => $value1) {
     			// var_dump($value1);die();
				$stdata = array(
					'product_code' => $value1['product_code'],
					'in_id' => $in_id,
					'product_quantity' => $value1['qty'] ,
					'product_price' => $value1['unit_price'],
					'products_money' => $value1['qty']* $value1['unit_price'],
					'date_added' => $karr[0].'-01  11:32:22',
				 );
				// var_dump($stdata);
				M('stock_in_detail')->add($stdata);
				$price +=$value1['qty']* $value1['unit_price'];

				// echo M('stock_in_detail')->getLastsql();die();

			}
			M('stock_in')->data(array('buy_money'=>$price))->where(array('in_id'=>$in_id))->save();	
			$shdata = array(
				'in_id' => $in_id, 
     			'user_id' => 160,
				'operator_name' =>'永康物流营销部', 
				'comment' =>'excel导入',
				'date_added' => $karr[0].'-01  11:32:22', 
			);
			M('stock_in_history')->add($shdata);
			$shdata = array(
				'po_id' => $po_id, 
     			'user_id' => 160,
				'operator_name' =>'永康物流营销部', 
				'comment' =>'excel导入', 
				'date_added' => $karr[0].'-01  11:32:22',
			);
			M('po_history')->add($shdata);
     		$this->addInven(10,$value,2,$in_id,$karr[0].'-01  11:32:22');
     	}
    }


	public function addInven($warehouse_id,$data,$refer_type_id, $in_id=0,$time){
		// var_dump($data);die();
		foreach ($data as $key => $value) {
			$value['product_code'] = $value['product_code'];
			$productNum = M('inventory')
			->field('id,available_quantity,product_code,account_quantity,qty_in_onway')
			->where(array('product_code' => $value['product_code'],'warehouse_id'=>$warehouse_id))
			->find();
			if (empty($productNum)) {
				$data = array(
					'warehouse_id'=>$warehouse_id, 
		  			'product_code'=>$value['product_code'],
		  		 	'available_quantity'=>$value['qty'], 
		  		 	'account_quantity'=>$value['qty'], 
					'date_added'=>$time);
			  	$inventoryPK = M('inventory')->data($data)->add();
			}else{
				$total=(int)$productNum['available_quantity']+(int)$value['qty'];
				$totalc=(int)$productNum['account_quantity']+(int)$value['qty'];
				M('inventory')-> where(array('product_code' => $value['product_code'],'warehouse_id'=>$warehouse_id))->data(array('available_quantity'=>$total,'account_quantity'=>$totalc,'date_modified'=>date('Y-m-d H:i:s')))->save();
				if ($refer_type_id==2) {
					$totalo=max(0, (int)$productNum['qty_in_onway']-(int)$value['qty']);
					M('inventory')-> where(array('product_code' => $value['product_code'],'warehouse_id'=>$warehouse_id))->data(array('qty_in_onway'=>$totalo,'date_modified'=>date('Y-m-d H:i:s')))->save();
				}
                $inventoryPK = $productNum['id'];
			}

            /*
             * 入库单修改库存后保存库存变更日志
             * @author sonicsjh
             */

            // var_dump($value['qty']);die();

            if (0 != (int)$value['qty']){
                $inventoryHistoryData = array(
                    'inventory_id'  => $inventoryPK,//inventory表PK
                    'product_code'  => $value['product_code'],//商品编码（冗余字段）
                    'warehouse_id'  => $warehouse_id,//仓库编号（冗余字段）
                    'account_qty'   => (int)$value['qty'],//财务数量变更（正+ 负-）
                    'available_qty' => (int)$value['qty'],//可用数量变更（正+ 负-）
                    'comment'       => '入库单：'.$in_id.' 完成入库。',//备注（入库单完成、损益单完成、出库单生成、出库单修改、出库单作废）
                    'user_id'       => 160,//操作人PK
                    'date_added'    => $time,//条目创建时间
                );
                M('inventory_history')->data($inventoryHistoryData)->add();
                // echo  M('inventory_history')->getLastsql();
            }

			//$this->addInventoryfront($value['product_code'],$value['qty']);
		}
	}

	public function addInvenou($warehouse_id,$data,$refer_type_id, $in_id=0,$time){
		// var_dump($data);die();
		foreach ($data as $key => $value) {
			$value['product_code'] = $value['product_code'];
			$productNum = M('inventory')
			->field('id,available_quantity,product_code,account_quantity,qty_in_onway')
			->where(array('product_code' => $value['product_code'],'warehouse_id'=>$warehouse_id))
			->find();
			if (empty($productNum)) {
				continue;
			}else{
				$total=(int)$productNum['available_quantity']-(int)$value['qty'];
				$totalc=(int)$productNum['account_quantity']-(int)$value['qty'];
				M('inventory')-> where(array('product_code' => $value['product_code'],'warehouse_id'=>$warehouse_id))->data(array('available_quantity'=>$total,'account_quantity'=>$totalc,'date_modified'=>date('Y-m-d H:i:s')))->save();
                $inventoryPK = $productNum['id'];
			}

            /*
             * 入库单修改库存后保存库存变更日志
             * @author sonicsjh
             */

            // var_dump($value['qty']);die();
           $qty   = (int)$value['qty'];
           $qty   = -$qty;

            if (0 != (int)$value['qty']){
                $inventoryHistoryData = array(
                    'inventory_id'  => $inventoryPK,//inventory表PK
                    'product_code'  => $value['product_code'],//商品编码（冗余字段）
                    'warehouse_id'  => $warehouse_id,//仓库编号（冗余字段）
                    'account_qty'   => $qty ,//财务数量变更（正+ 负-）
                    'available_qty' => $qty ,//可用数量变更（正+ 负-）
                    'comment'       => '出库单：'.$in_id.' 完成出库。',//备注（入库单完成、损益单完成、出库单生成、出库单修改、出库单作废）
                    'user_id'       => 160,//操作人PK
                    'date_added'    => $time,//条目创建时间
                );
                M('inventory_history')->data($inventoryHistoryData)->add();
                // echo  M('inventory_history')->getLastsql();
            }

			//$this->addInventoryfront($value['product_code'],$value['qty']);
		}
	}




	public function addShop(){
		$sql = "SELECT * FROM `customer` where need_review=0 AND customer_id>6915  GROUP BY customer_id";
		$query = $this->db->query($sql);
		$address = $query->rows;
		foreach ($address as $key => $value) {
			if (!empty($value['customer_id'])) {
				$shopin = M('shop')->where(array('customer_id'=>$value['customer_id']))->find();
				if (empty($shopin)) {
					$data = array(
					// 'address'=>$value['address'],
					'customer_id'=>$value['customer_id'],
					// 'country_id'=>$value['country_id'],
					// 'country'=>$value['country'],
					// 'zone_id'=>$value['zone_id'],
					// 'zone'=>$value['zone'],
					// 'city_id'=>$value['city_id'],
					// 'city'=>$value['city'],
					// 'lng'=>$value['lng'],
					// 'lat'=>$value['lat'],
					'date_added'=>date("Y-m-d H:i:s"),
					'telephone'=>$value['telephone'],
					// 'shop_name'=>$value['company_name'],
					// 'level'=>3,
				);
				$shop_id = M('shop')->add($data);
				M('customer')->where(array('customer_id'=>$value['customer_id']))->save(array('shop_id'=>$shop_id));
				}
				
			}
			

		}
	}

	//回退库存
	public function returnIn(){
		$sql = "SELECT SUM(available_qty) as 'available_qty',SUM(account_qty) as 'account_qty',`inventory_history`.* FROM `inventory_history`  WHERE date_added >'20180401000000' AND date_added <'20180501000000' GROUP BY product_code";

		$query = $this->db->query($sql);
		$address = $query->rows;
		foreach ($query->rows as $key => $value) {
			// var_dump($value);
			$productNum = M('inventory')
			->field('id,available_quantity,product_code,account_quantity,qty_in_onway')
			->where(array('product_code' => $value['product_code'],'warehouse_id'=>10))
			->find();
			// // echo M('inventory')->getLastsql();die();

			// // echo M('inventory')->getLastsql();die();

			if (empty($productNum)) {
				$data = array(
					'warehouse_id'=>10, 
		  			'product_code'=>$value['product_code'],
		  		 	'available_quantity'=>$value['available_qty'], 
		  		 	'account_quantity'=>$value['account_qty'], 
					'date_added'=>date('Y-m-d H:i:s'));
			  	$inventoryPK = M('inventory')->data($data)->add();
			}else{
				
				$total=(int)$productNum['available_quantity']+(int)$value['available_qty'];
				$totalc=(int)$productNum['account_quantity']+(int)$value['account_qty'];
				// var_dump($total);
				// var_dump($totalc);
				// die();
				M('inventory')-> where(array('product_code' => $value['product_code'],'warehouse_id'=>10))->save(array('available_quantity'=>$total,'account_quantity'=>$totalc,'date_modified'=>date('Y-m-d H:i:s')));
			}


		}

		
	}
	public function importin() {
		$json = array();
		if (1) {
		  if (!empty($this->request->files['file']['name']) && is_file($this->request->files['file']['tmp_name'])) {
			// Sanitize the filename
			$filename = basename(html_entity_decode($this->request->files['file']['name'], ENT_QUOTES, 'UTF-8'));
			// Validate the filename length
			if ((utf8_strlen($filename) < 3) || (utf8_strlen($filename) > 128)) {
			  $json['error'] = '文件名过短';
			}
			// Allowed file extension types
			$allowed = array('xls','xlsx');
			if (!in_array(strtolower(substr(strrchr($filename, '.'), 1)), $allowed)) {
			  $json['error'] = '请上传xls或者xlsx文件';
			}
			// Return any upload error
			if ($this->request->files['file']['error'] != UPLOAD_ERR_OK) {
			  $json['error'] = '上传出现错误';
			}
		  } 
		}
		else {
		  $json['error'] = '上传失败';
		}

		if (!$json) {
		  $this->load->library('PHPExcel/PHPExcel');
		  $savePath = $this->request->files['file']['tmp_name'];
		  $PHPExcel = new PHPExcel();
		  $PHPReader = new PHPExcel_Reader_Excel2007();
		  if(!$PHPReader->canRead($savePath)){
			$PHPReader = new PHPExcel_Reader_Excel5();
		  }
		  $PHPExcel = $PHPReader->load($savePath);
		  $currentSheet = $PHPExcel->getSheet(0);
		  $allRow = $currentSheet->getHighestRow();
		  //获取order_product_id, express_name, express_number在excel中的index
		  $sku_idx = $pcode = $accountnum = $availablenum = 0;
		  $tmp_idx = 'A'; //假设导入表格不会长于Z
		  $currentRow = 1;
		  // var_dump($currentSheet->getCell($tmp_idx.$currentRow)->getValue());
		  while(($tmp_idx === 0 || $pcode === 0 || $accountnum === 0  )&&$tmp_idx<'Z') {
			switch (trim((String)$currentSheet->getCell($tmp_idx.$currentRow)->getValue())) {
			  case '仓库':
				$sku_idx = $tmp_idx;
				break;
			  case '商品编码':
				$pcode = $tmp_idx;
				break;
			  case '可用数量':
				$accountnum = $tmp_idx;
				break;
			}
			$tmp_idx++;
		  }
		  if(0 === $sku_idx || 0 === $pcode||0 === $accountnum) {
			$json['error'] = '请检查文件第一行是否有';
		  }
		  
		  if (!$json) {
			$this->load->model('catalog/product');
			$products_info = array();
			// var_dump($allRow);die();
			for ($currentRow = 2;$currentRow <= $allRow;$currentRow++) {
			  $sku = (String)$currentSheet->getCell($sku_idx.$currentRow)->getValue();
			  $sku = trim($sku );
			  $pcode1 = (String)$currentSheet->getCell($pcode.$currentRow)->getValue();
			  $accountnum1 = (int)$currentSheet->getCell($accountnum.$currentRow)->getValue();
			  $total +=$accountnum1;
			  // var_dump($accountnum);
			  switch ($sku) {
			  	case '嘉兴分仓':
			  		$warehouse_id = 15;
			  		break;
			  	case '南通分仓':
			  		$warehouse_id = 5;
			  		break;
			  	case '台州分仓':
			  		$warehouse_id = 25;
			  		break;
			  	case '温州分仓':
			  		$warehouse_id = 13;
			  		break;
			  	case '无锡总仓':
			  		$warehouse_id = 4;
			  		break;
			  	case '无锡残次品仓':
			  		$warehouse_id = 19;
			  		break;
			  	case '永康总仓':
			  		$warehouse_id = 10;
			  		break;
			  	case '永康残次品仓':
			  		$warehouse_id = 17;
			  		break;
			  }
			  $data = array(
					'warehouse_id'=>$warehouse_id, 
		  			'product_code'=>$pcode1,
		  		 	'available_quantity'=>$accountnum1, 
		  		 	'account_quantity'=>$accountnum1, 
					'date_added'=>'2018-01-01 00:00:00'
				);
			 M('inventory')->data($data)->add();
			 // echo  M('inventory')->getLastsql();



			}        
		
		  }
		}

		echo $total;

		// $this->response->addHeader('Content-Type: application/json');
		// $this->response->setOutput(json_encode($json));

	}

	public function updateShop(){

		$sql = "SELECT shop.shop_id FROM `shop`";
		$query = $this->db->query($sql);
		$shop = $query->rows;
		$i = 90001;
		foreach ($shop as $key => $value) {
			M("shop")->where(array("shop_id"=>$value['shop_id']))->save(array("qrcode_id"=>$i));
			$i++;
		}
		
	}

	public function moveInventory(){

		$sql = "SELECT inventory.id, inventory.warehouse_id, inventory.product_code, inventory.position1, inventory.position2, inventory.account_quantity, inventory.available_quantity, inventory.safe_quantity, inventory.qty_in_onway, inventory.qty_out_onway, inventory.`status`, inventory.date_added, inventory.date_modified FROM inventory WHERE inventory.warehouse_id=17";
		$query = $this->db->query($sql);
		$inventoryP = $query->rows;
		foreach ($query->rows as $key => $value) {
			// var_dump($value);
			$productNum = M('inventory')
			->field('id,available_quantity,product_code,account_quantity,qty_in_onway,qty_out_onway,safe_quantity')
			->where(array('product_code' => $value['product_code'],'warehouse_id'=>19))
			->find();
	 // echo M('inventory')->getLastsql();die();

			// // echo M('inventory')->getLastsql();die();

			if (empty($productNum)) {
				$data = array(
					'warehouse_id'=>19, 
		  			'product_code'=>$value['product_code'],
		  		 	'available_quantity'=>$value['available_quantity'], 
		  		 	'account_quantity'=>$value['account_quantity'], 
		  		 	'safe_quantity'=>$value['safe_quantity'], 
		  		 	'qty_in_onway'=>$value['qty_in_onway'], 
		  		 	'qty_out_onway'=>$value['qty_out_onway'], 
					'date_added'=>date('Y-m-d H:i:s')
				);
				// var_dump($data);
				// die();
			  	$inventoryPK = M('inventory')->data($data)->add();
			  	// echo M('inventory')->getLastsql();
			  	// die();
			}else{
				
				$available_quantity=(int)$productNum['available_quantity']+(int)$value['available_quantity'];
				$account_quantity=(int)$productNum['account_quantity']+(int)$value['account_quantity'];
				$safe_quantity=(int)$productNum['safe_quantity']+(int)$value['safe_quantity'];
				$qty_in_onway=(int)$productNum['qty_in_onway']+(int)$value['qty_in_onway'];
				$qty_out_onway=(int)$productNum['qty_out_onway']+(int)$value['qty_out_onway'];
				// var_dump($available_quantity);
				// var_dump($account_quantity);
				// var_dump($safe_quantity);
				// var_dump($qty_in_onway);
				// var_dump($qty_out_onway);
				// die();
				M('inventory')-> where(array('product_code' => $value['product_code'],'warehouse_id'=>19))->save(array(
					'available_quantity'=>$available_quantity,
					'account_quantity'=>$account_quantity,
					'safe_quantity'=>$safe_quantity,
					'qty_in_onway'=>$qty_in_onway,
					'qty_out_onway'=>$qty_out_onway,
					'date_modified'=>date('Y-m-d H:i:s')));
			}


		}
	}


	public function copyProduct(){

		$sql = "SELECT sp.product_id, sp.shop_id,sp.shelf_id, p.sku, p.image, pd.`name`, pd.description, p.product_class1,p.price, p.product_class2, p.product_class3 FROM shelf_product AS sp LEFT JOIN product AS p ON sp.product_id = p.product_id LEFT JOIN product_description AS pd ON p.product_id = pd.product_id WHERE  sp.`shelf_product_id`>62203";
		$query = $this->db->query($sql);
		$copyproduct = $query->rows;
		foreach ($copyproduct as $key => $value) {
			$shop_id= $value['shop_id'];
			$product_id = $value['product_id'];
			$data[$shop_id][$product_id] = array(

					'shop_id'=>$value['shop_id'], 
					'sku'=>$value['sku'], 
					'image'=>$value['image'], 
					'product_id'=>$value['product_id'], 
					'name'=>$value['name'], 
					'shop_basic_code'=>$value['shop_id'], 
					'description'=>$value['description'], 
					'product_class1'=>$value['product_class1'], 
					'product_class2'=>$value['product_class2'], 
					'product_class3'=>$value['product_class3'], 
					'shelf_id'=>$value['shelf_id'], 
					'price'=>$value['price'], 
					'sale_price'=>$value['price'], 
		  			
			);
			// $shop_product_id = M('shop_product')->add($data);
			// // echo M('shop_product')->getLastsql();die();

			// $data = array(
			// 		'shop_product_id'=>$shop_product_id, 
			// 		'name'=>'通用', 
		  			
			// );
			// M('shop_product_option')->add($data);
		}
		// var_dump($data);die();
		foreach ($data as $key => $value) {
			foreach ($value as $key1 => $value1) {
				if (empty($value1['sku'])) {
					continue;
				}
				$find = M('shop_product')->where(array('product_id'=>$value1['product_id'],'shop_id'=>$value1['shop_id']))->find();
				if (!empty($find)) {
					continue;
				}
				 $data1= array(
					'shop_id'=>$value1['shop_id'], 
					'sku'=>$value1['sku'], 
					'image'=>$value1['image'], 
					'product_id'=>$value1['product_id'], 
					'name'=>$value1['name'], 
					'shop_basic_code'=>$value1['shop_id'], 
					'description'=>$value1['description'], 
					'product_class1'=>$value1['product_class1'], 
					'product_class2'=>$value1['product_class2'], 
					'product_class3'=>$value1['product_class3'], 
					'shelf_id'=>$value1['shelf_id'], 
					'price'=>$value1['price'], 
					'sale_price'=>$value1['price'], 
					'date_added'=>date('Y-m-d H:i:s')
		  			
				);
				 $shop_product_id = M('shop_product')->add($data1);
				 // var_dump($shop_product_id);die();
				// echo M('shop_product')->getLastsql();die();

				$data = array(
						'shop_product_id'=>$shop_product_id, 
						'name'=>'通用', 
			  			
				);
				M('shop_product_option')->add($data);
			}

			
		}
		
	}

// CREATE TABLE `product_image` (
//   `product_image_id` int(11) NOT NULL AUTO_INCREMENT,
//   `product_id` int(11) NOT NULL,
//   `image` varchar(255) DEFAULT NULL,
//   `sort_order` int(3) NOT NULL DEFAULT '0',
//   PRIMARY KEY (`product_image_id`),
//   KEY `product_id` (`product_id`)
// ) ENGINE=MyISAM AUTO_INCREMENT=110328 DEFAULT CHARSET=utf8;


	public function addimage(){

		$sql = "SELECT shop_product.shop_product_id, shop_product.product_id FROM `shop_product` WHERE  `date_added` LIKE  '%2018-05-18%'"; 
		$query = $this->db->query($sql);

		$copyproduct = $query->rows;
		foreach ($copyproduct as $key => $value) {
			$sql = "SELECT product_image.product_image_id, product_image.product_id, product_image.image, product_image.sort_order FROM `product_image`
			 where product_image.product_id=".$value['product_id'];
			$query = $this->db->query($sql);

			$images = $query->rows;
			foreach ($images as $key2 => $value2) {
				 $dataq= array(
					'shop_product_id'=>$value['shop_product_id'], 
					'product_id'=>$value['product_id'], 
					'image'=>$value2['image'], 
		  			
				);
				 // var_dump($dataq);
				M('shop_product_image')->add($dataq);
			// echo M('product_image')->getLastsql();die();

			}
			
		}

		
	}


	public function updatePcode(){

		$sql = "SELECT shop_product.shop_product_id, shop_product.product_class1, shop_product.product_class2, shop_product.product_class3, shop_product.shop_basic_code, shop_product.sku, shop_product.price, shop_product.sale_price, shop_product.`status`, shop_product.date_added, shop_product.shop_id, shop_product.shelf_id, shop_product.`name`, shop_product.description, shop_product.product_id FROM `shop_product`";
		$query = $this->db->query($sql);

		$products = $query->rows;
		foreach ($products as $key => $value) {
			
			$data[$value['shop_id']][] = $value;
			
			
		}

		foreach ($data as $key1 => $value1) {
			$i=0;
			foreach ($value1 as $key2 => $value2) {
				$i++;
				$str =  $value2['shop_id'].'_'.$i;
				M('shop_product')->where(array('shop_product_id'=>$value2['shop_product_id']))->save(array('shop_basic_code'=>$str));
				
			}
		}

		
	}

	public function updateOcode(){

		$sql = "SELECT shop_product.shop_product_id, shop_product.product_class1, shop_product.product_class2, shop_product.product_class3, shop_product.shop_basic_code, shop_product.sku, shop_product.price, shop_product.sale_price, shop_product.`status`, shop_product.date_added, shop_product.shop_id, shop_product.shelf_id, shop_product.`name`, shop_product.description, shop_product.product_id FROM `shop_product`";
		$query = $this->db->query($sql);

		$products = $query->rows;

		foreach ($products as $key => $value) {
			// $shop_product_option_id = M('shop_product_option')->where(array('shop_product_id'=>$value['shop_product_id']))->getField("shop_product_option_id");
			// echo M('shop_product_option')->getLastsql();die();

			$str =  $value['shop_basic_code'].'_1';
			// var_dump($value['shop_basic_code']);

			// var_dump($str);die();
			M('shop_product_option')->where(array('shop_product_id'=>$value['shop_product_id']))->save(array('shop_product_code'=>$str));
				
			
		}

		
	}

	public function upShopInfo(){

		$sql = "SELECT op.order_product_id, op.order_id, op.product_id, op.product_code, op.product_type, o.order_id, o.customer_id FROM order_product AS op LEFT JOIN `order` AS o ON op.order_id = o.order_id WHERE o.order_id >= 9376 AND o.order_status_id<>16";
		$query = $this->db->query($sql);

		$products = $query->rows;

		foreach ($products as $key => $value) {
			$customer_id = $value['customer_id'];
			$product_code = $value['product_code'];
			$product_id = $value['product_id'];
			if ($value['product_type']=1) {
				$ret[$customer_id][$product_code]['product_code'] = $product_code;
				$ret[$customer_id][$product_code]['product_id'] = $product_id;
			}else{
				$sql = "SELECT opg.order_product_group_id, opg.order_product_id, opg.product_id, opg.product_code FROM order_product_group AS opg where opg.order_product_id =".$value['order_product_id'];
				$query = $this->db->query($sql);
				foreach ($query->rows as $key1 => $value1) {
					$product_code1 = $value1['product_code'];
					$ret[$customer_id][$product_code1]['product_code'] = $product_code1;
					$ret[$customer_id][$product_code1]['product_id'] = $product_id;
					
				}

			}
			
		}

		foreach ($ret as $key => $value) {

			$sql = "SELECT s.shelf_id, s.customer_id, s.shelf_category FROM shelf AS s where s.shelf_category=855 AND s.customer_id=".$key;
			// echo $sql;
			$query = $this->db->query($sql);
			$shelf_id = $query->row['shelf_id'];
			// var_dump($query->row);
			// var_dump($shelf_id);
			// die();
			$customer_id = $key;
			if ($shelf_id) {
				foreach ($value as $key1 => $value1) {
					$data['product_id'] = $value1['product_id'];
					$data['product_code'] = $value1['product_code'];
					$data['shelf_id'] = $shelf_id;
					$data['date_added'] = date('Y-m-d H:i:s');
					M('shelf_product')->add($data);
				}
			}else{

				$data = array(
					'customer_id'=>$customer_id,
					'shelf_name'=>'综合性品类货架',
					'shelf_category'=>855,
					'status'=>1,
					'date_enter'=>date('Y-m-d H:i:s'),
					'date_added'=>date('Y-m-d H:i:s'),
				);
				$shelf_id =  M('shelf')->add($data);

				foreach ($value as $key1 => $value1) {

					$data = array(
						'shelf_id'=>$shelf_id,
						'product_id'=>$value1['product_id'],
						'product_code'=>$value1['product_code'],
						'date_added'=>date('Y-m-d H:i:s'),
					);
					 M('shelf_product')->add($data);

				}
			}


		}

			

		
	}

			  

	public function upSp(){
		$product = M('product')->select();
		foreach ($product as $key => $value) {
			$this->load->model('catalog/mvd_product');
			
			$special=0;
			$product_specials = $this->model_catalog_mvd_product->getgroupProductSpecials($value['product_id'],1);

			foreach ($product_specials  as $product_special) {
				if (($product_special['date_start'] == '0000-00-00' || strtotime($product_special['date_start']) < time()) && ($product_special['date_end'] == '0000-00-00' || strtotime($product_special['date_end']) > time())) {
				
					$special = $product_special['price'];

					break;
				}
			}
			if ((int)$special==0) {
				// echo 11;
				$special = $value['sale_price'];
				
			}
			$spdata = array(
		   		'product_id' =>$value['product_id'] ,
		   		'customer_group_id' => 2,
		   		'price' => round($special*1.11,1),
		   		'date_start' => date("Y-m-d"),
		   		'date_end' => '2050-01-01',
		   		 );
		   	M('product_special')->add($spdata);
		}
		
	}
//把清仓商品加减尽杀敌
	public function addtoCa(){
		$sql = "SELECT * FROM  `product` WHERE  `clearance` =1";
		$query = $this->db->query($sql);
	
		foreach ($query->rows as $key => $value) {
			if (!empty($value['product_id'])) {
				$sql1 = "SELECT * FROM  `product_to_category` WHERE  `product_id` =".$value['product_id']."
				  AND category_id =179 ";
				  // echo $sql;

				$query1 = $this->db->query($sql1);
				if (!empty($query1->row)) {
					continue;
				}
				$data = array(
					'product_id'=>$value['product_id'],
					'category_id'=>179
				);
				M('product_to_category')->add($data);
			}
			

		}
	}		




}
